package com.ssy.lingxi.report.dao.aftersale;

import com.ssy.lingxi.common.constant.settle.SettlementTypeEnum;
import com.ssy.lingxi.report.entity.settleaccount.MemberOrderSettlementDO;
import com.ssy.lingxi.report.entity.settleaccount.PlatformSettlementDetailDO;
import com.ssy.lingxi.report.model.bo.MemberReceiptInvoiceBO;
import com.ssy.lingxi.report.model.bo.MemberSettlementBO;
import com.ssy.lingxi.report.model.bo.PlatformSettlementBO;
import com.ssy.lingxi.report.model.bo.tally.statistics.AfterSaleReturnApplyStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.AfterSaleReturnHandleStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 售后退货DAO
 * @author 伍将
 * @date 2020/11/14
 * @version 2.0.0
 */
@Service
public class ReturnGoodsDAO {

    @Autowired
    @Qualifier("afterSaleJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询售后退货申请统计数量
     * @return 售后退货申请统计数量
     */
    public List<AfterSaleReturnApplyStatisticsTallyVO> getApplyStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id,role_id,")
                .append(" sum(case when outer_status in(1,3) then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when outer_status = 5 then 1 else 0 end) toBeAddDeliveryCount,")
                .append(" sum(case when outer_status in(6,23) then 1 else 0 end) toBeAddLogisticsCount,")
                .append(" sum(case when outer_status = 7 then 1 else 0 end) toBeDeliveryCount,")
                .append(" sum(case when outer_status = 10 then 1 else 0 end) toBeConfirmReceiptCount,")
                .append(" sum(case when outer_status = 12 then 1 else 0 end) toBeConfirmRefundCount,")
                .append(" sum(case when outer_status = 14 then 1 else 0 end) toBeConfirmCompleteCount")
                .append(" FROM as_return_goods ")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(AfterSaleReturnApplyStatisticsTallyVO.class));
    }

    /**
     * 查询售后退货处理统计数量
     * @return 退货处理统计数量
     */
    public List<AfterSaleReturnHandleStatisticsTallyVO> getHandleStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" parent_member_id member_id,parent_member_role_id role_id,")
                .append(" sum(case when inner_status = 1 then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when inner_status = 2 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when inner_status = 3 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when inner_status in(4,5,24,25) then 1 else 0 end) toBeConfirmCount,")
                .append(" sum(case when outer_status = 8 then 1 else 0 end) toBeAddStorageCount,")
                .append(" sum(case when outer_status = 9 then 1 else 0 end) toBeReceiveCount,")
                .append(" sum(case when outer_status in(11,13) then 1 else 0 end) toBeRefundCount")
                .append(" FROM as_return_goods ")
                .append(" where outer_status>1")
                .append(" group by parent_member_id,parent_member_role_id")
                .append(" union")
                .append(" select 0 member_id,0 role_id,")
                .append(" 0 toBeCommitValifyCount,")
                .append(" 0 toBeValifyStep1Count,")
                .append(" 0 toBeValifyStep2Count,")
                .append(" 0 toBeConfirmCount,")
                .append(" 0 toBeAddStorageCount,")
                .append(" 0 toBeReceiveCount,")
                .append(" sum(case when outer_status in(11,13) then 1 else 0 end) toBeRefundCount")
                .append(" FROM as_return_goods")
                .append(" where outer_status>1;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(AfterSaleReturnHandleStatisticsTallyVO.class));
    }

    /**
     * 获取平台代收账款结算
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return 平台代收账款结算
     **/
    public PlatformSettlementBO getSettlement(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(distinct re.id) totalCount,COALESCE(sum(refund.refund_amount),0) totalAmount")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=1")    // 平台代收
                .append(" and re.refund_amount>0")
                .append(" and refund.confirm_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId);

        List<PlatformSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementBO.class));

        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalAmount() > 0).findFirst().orElse(null);
    }

    public PlatformSettlementBO getSettlementBak(Integer settlementPaymentType, Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        String appendStr = SettlementTypeEnum.ALLIN_PAY.getCode().equals(settlementPaymentType)
                ? " and refund.channel in (11,12,13,14,15)" : " and refund.channel not in (11,12,13,14,15)" ;

        sql.append(" select ")
                .append(" count(distinct re.id) totalCount,COALESCE(sum(refund.refund_amount),0) totalAmount")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=1")    // 平台代收
                .append(appendStr)
                .append(" and re.refund_amount>0")
                .append(" and refund.confirm_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId);

        List<PlatformSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementBO.class));

        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalAmount() > 0).findFirst().orElse(null);

    }
    /**
     * 获取平台代收账款结算明细
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return :
     **/
    public List<PlatformSettlementDetailDO> getSettlementDetail(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" re.apply_no orderNo,re.apply_abstract orderAbstract,re.create_time orderTime,re.refund_amount orderAmount,refund.refund_amount collectAmount,refund.confirm_time pay_time,")
                .append(" coalesce(detail.is_has_tax,0) isHasTax,coalesce(detail.tax_rate,0) taxRate")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=1")    // 平台代收
                .append(" and re.refund_amount>0")
                .append(" and refund.confirm_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId);

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementDetailDO.class));
    }

    /**
     * @author 彭冬冬
     * @date 2021/12/20
     * @param settlementPaymentType 结算支付方式
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param memberId 会员id
     * @param roleId 角色id
     * @return 结算退款符合的平台结算明细列表
     */
    public List<PlatformSettlementDetailDO> getSettlementDetailBak(Integer settlementPaymentType, Long startTime, Long endTime, Long memberId, Long roleId){
        StringBuilder sql = new StringBuilder();
        String appendStr = SettlementTypeEnum.ALLIN_PAY.getCode().equals(settlementPaymentType)
                ? " and refund.channel in (11,12,13,14,15)" : " and refund.channel not in (11,12,13,14,15)" ;
        sql.append(" select ")
                .append(" re.apply_no orderNo,re.apply_abstract orderAbstract,re.create_time orderTime,re.refund_amount orderAmount,refund.refund_amount collectAmount,refund.confirm_time pay_time,")
                .append(" coalesce(detail.is_has_tax,0) isHasTax,coalesce(detail.tax_rate,0) taxRate, refund.pay_id paymentId, refund.transaction_pay_id paymentTradeNo ")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=1")    // 平台代收
                .append(appendStr)
                .append(" and re.refund_amount>0")
                .append(" and refund.confirm_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId);

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementDetailDO.class));
    }

    /**
     * 分页查询退货票据报表
     * @author 伍将
     * @date 2020/12/16
     * @param index:
     * @param count:
     * @return 退货票据报表
     **/
    public List<MemberReceiptInvoiceBO> pageInvoiceReport(Integer index, Integer count) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" re.parent_member_id member_id,re.parent_member_role_id role_id,re.apply_no order_no,re.apply_abstract order_abstract,'5' settlement_order_type,")
                .append(" '999' order_type,re.apply_time order_time_temp,re.refund_amount order_amount,detail.pay_count batch,detail.pay_node,-detail.refund_amount pay_amount,")
                .append(" CASE WHEN detail.inner_status>1 THEN detail.refund_time ELSE 0 end pay_time_temp,'0' invoice_status, CASE WHEN detail.inner_status>1 THEN 1 ELSE 0 end pay_status,")
                .append(" detail.id data_id,re.parent_member_id payMemberId,re.parent_member_role_id payRoleId,")
                .append(" coalesce(goods.is_has_tax,0) isHasTax,coalesce(goods.tax_rate,0) taxRate")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail detail")
                .append(" on re.id=detail.return_id")
                .append(" inner join as_return_goods_detail goods")
                .append(" on goods.id=detail.detail_id")
                .append(" where re.outer_status>=4")    //已接受申请
                .append(" and re.need_invoice=1")    //需要发票
                .append(" order by detail.id ")
                .append(" limit " + count + " offset " + index + ";");  //分页查询

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberReceiptInvoiceBO.class));
    }

    /**
     * 获取采购退款单时段内总数(用于结算)
     * @author ds
     * @date 2021/4/1
     **/
    public MemberSettlementBO getSettlementByPurchase(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId,Integer payType) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(distinct re.id) totalCount,COALESCE(sum(refund.refund_amount),0) totalAmount")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=2")    //会员直接到账
                .append(" and re.refund_amount>0")
                .append(" and refund.channel="+payType)    //支付渠道
                .append(" and refund.refund_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId)
                .append(" and re.member_id=" + payMemberId + " and re.role_id=" + payRoleId);
        List<MemberSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberSettlementBO.class));

        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalAmount() > 0).findFirst().orElse(null);
    }

    /**
     * 获取采购退款单明细(用于结算)
     * @author ds
     * @date 2021/4/1
     **/
    public List<MemberOrderSettlementDO> getSettlementDetailByPurchase(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId,Integer payType) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" re.apply_no orderNo,re.apply_abstract orderAbstract,re.create_time orderTime,re.refund_amount orderAmount,refund.refund_amount payAmount,refund.refund_time pay_time,")
                .append(" coalesce(detail.is_has_tax,0) isHasTax,coalesce(detail.tax_rate,0) taxRate,refund.pay_count payCount,refund.id dataId")
                .append(" from as_return_goods re")
                .append(" inner join as_return_goods_refund_detail refund")
                .append(" on re.id=refund.return_id")
                .append(" inner join as_return_goods_detail detail")
                .append(" on detail.id=refund.detail_id")
                .append(" where refund.outer_status=4")  // 确认到账
                .append(" and refund.pay_rule_id=2")    //会员直接到账
                .append(" and re.refund_amount>0")
                .append(" and refund.channel="+payType)    //支付渠道
                .append(" and refund.refund_time between " + startTime + " and " + endTime)
                .append(" and re.parent_member_id=" + memberId + " and re.parent_member_role_id=" + roleId)
                .append(" and re.member_id=" + payMemberId + " and re.role_id=" + payRoleId);

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberOrderSettlementDO.class));
    }
}
